﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using PPAP.Model.Entity;
using System.Data;

namespace PPAP.DAO
{
    /// <summary>
    /// 数据访问类SUPPLIER。
    /// </summary>
    public class SUPPLIERDAO
    {
        public SUPPLIERDAO()
        { }
        #region  成员方法

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string USERNAME)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from SUPPLIER");
            strSql.Append(" where USERNAME=:USERNAME ");
            OracleParameter[] parameters = {
					new OracleParameter(":USERNAME", OracleType.VarChar,50)};
            parameters[0].Value = USERNAME;

            return DbHelperOra.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SUPPLIER model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "SUPPLIER");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SUPPLIER(");
            strSql.Append("ID,SUPPLIERNAME,USERNAME,PASSWORD,DSC,CONTACTTEL,ADDRESS,SUPPLIERCODE,SUBMISSIONLEVEL,COUNTRY,PROVINCE,CITY,POSTALCODE)");
            strSql.Append(" values (");
            strSql.Append(":ID,:SUPPLIERNAME,:USERNAME,:PASSWORD,:DSC,:CONTACTTEL,:ADDRESS,:SUPPLIERCODE,:SUBMISSIONLEVEL,:COUNTRY,:PROVINCE,:CITY,:POSTALCODE)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":SUPPLIERNAME", OracleType.VarChar,150),
					new OracleParameter(":USERNAME", OracleType.VarChar,50),
					new OracleParameter(":PASSWORD", OracleType.VarChar,50),
					new OracleParameter(":DSC", OracleType.VarChar,50),
					new OracleParameter(":CONTACTTEL", OracleType.VarChar,50),
					new OracleParameter(":ADDRESS", OracleType.VarChar,50),
					new OracleParameter(":SUPPLIERCODE", OracleType.VarChar,50),
					new OracleParameter(":SUBMISSIONLEVEL", OracleType.VarChar,50),
					new OracleParameter(":COUNTRY", OracleType.VarChar,50),
					new OracleParameter(":PROVINCE", OracleType.VarChar,50),
					new OracleParameter(":CITY", OracleType.VarChar,50),
					new OracleParameter(":POSTALCODE", OracleType.VarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.SUPPLIERNAME;
            parameters[2].Value = model.USERNAME;
            parameters[3].Value = model.PASSWORD;
            parameters[4].Value = model.DSC;
            parameters[5].Value = model.CONTACTTEL;
            parameters[6].Value = model.ADDRESS;
            parameters[7].Value = model.SUPPLIERCODE;
            parameters[8].Value = model.SUBMISSIONLEVEL;
            parameters[9].Value = model.COUNTRY;
            parameters[10].Value = model.PROVINCE;
            parameters[11].Value = model.CITY;
            parameters[12].Value = model.POSTALCODE;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters)>0?int.Parse(model.ID.ToString()):0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(SUPPLIER model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update SUPPLIER set ");
            strSql.Append("SUPPLIERNAME=:SUPPLIERNAME,");
            strSql.Append("PASSWORD=:PASSWORD,");
            strSql.Append("DSC=:DSC,");
            strSql.Append("CONTACTTEL=:CONTACTTEL,");
            strSql.Append("ADDRESS=:ADDRESS,");
            strSql.Append("SUPPLIERCODE=:SUPPLIERCODE,");
            strSql.Append("SUBMISSIONLEVEL=:SUBMISSIONLEVEL,");
            strSql.Append("COUNTRY=:COUNTRY,");
            strSql.Append("PROVINCE=:PROVINCE,");
            strSql.Append("CITY=:CITY,");
            strSql.Append("POSTALCODE=:POSTALCODE,USERNAME=:USERNAME");
            strSql.Append(" where id=:id ");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":SUPPLIERNAME", OracleType.VarChar,150),
					new OracleParameter(":PASSWORD", OracleType.VarChar,50),
					new OracleParameter(":DSC", OracleType.VarChar,50),
					new OracleParameter(":CONTACTTEL", OracleType.VarChar,50),
					new OracleParameter(":ADDRESS", OracleType.VarChar,50),
					new OracleParameter(":SUPPLIERCODE", OracleType.VarChar,50),
					new OracleParameter(":SUBMISSIONLEVEL", OracleType.VarChar,50),
					new OracleParameter(":COUNTRY", OracleType.VarChar,50),
					new OracleParameter(":PROVINCE", OracleType.VarChar,50),
					new OracleParameter(":CITY", OracleType.VarChar,50),
					new OracleParameter(":POSTALCODE", OracleType.VarChar,50),
                                           new OracleParameter(":USERNAME",OracleType.VarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.SUPPLIERNAME;
            parameters[2].Value = model.PASSWORD;
            parameters[3].Value = model.DSC;
            parameters[4].Value = model.CONTACTTEL;
            parameters[5].Value = model.ADDRESS;
            parameters[6].Value = model.SUPPLIERCODE;
            parameters[7].Value = model.SUBMISSIONLEVEL;
            parameters[8].Value = model.COUNTRY;
            parameters[9].Value = model.PROVINCE;
            parameters[10].Value = model.CITY;
            parameters[11].Value = model.POSTALCODE;
            parameters[12].Value = model.USERNAME;

          return  DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(string USERNAME)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from SUPPLIER ");
            strSql.Append(" where USERNAME=:USERNAME ");
            OracleParameter[] parameters = {
					new OracleParameter(":USERNAME", OracleType.VarChar,50)};
            parameters[0].Value = USERNAME;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除供应商及其联系人
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteSupplier(int id)
        {
            string sql = "  delete contactperson where supplierid="+id;
            DbHelperOra.ExecuteSql(sql);
            sql = " delete supplier where id="+id;
            return DbHelperOra.ExecuteSql(sql) ;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public SUPPLIER GetModel(string id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from SUPPLIER ");
            strSql.Append(" where id=:id ");
            OracleParameter[] parameters = {
					new OracleParameter(":id", OracleType.VarChar,50)};
            parameters[0].Value = id;

            SUPPLIER model = new SUPPLIER();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.SUPPLIERNAME = ds.Tables[0].Rows[0]["SUPPLIERNAME"].ToString();
                model.USERNAME = ds.Tables[0].Rows[0]["USERNAME"].ToString();
                model.PASSWORD = ds.Tables[0].Rows[0]["PASSWORD"].ToString();
                model.DSC = ds.Tables[0].Rows[0]["DSC"].ToString();
                model.CONTACTTEL = ds.Tables[0].Rows[0]["CONTACTTEL"].ToString();
                model.ADDRESS = ds.Tables[0].Rows[0]["ADDRESS"].ToString();
                model.SUPPLIERCODE = ds.Tables[0].Rows[0]["SUPPLIERCODE"].ToString();
                model.SUBMISSIONLEVEL = ds.Tables[0].Rows[0]["SUBMISSIONLEVEL"].ToString();
                model.COUNTRY = ds.Tables[0].Rows[0]["COUNTRY"].ToString();
                model.PROVINCE = ds.Tables[0].Rows[0]["PROVINCE"].ToString();
                model.CITY = ds.Tables[0].Rows[0]["CITY"].ToString();
                model.POSTALCODE = ds.Tables[0].Rows[0]["POSTALCODE"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select s.*,rownum ");
            strSql.Append(" FROM SUPPLIER s ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 " + strWhere);
            }
            strSql.Append(" order by suppliercode");
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListForPage(string strWhere,int size,int index)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top {0} * from SUPPLIER where id not in ( select top {1}  ID ");
            strSql.Append(" FROM SUPPLIER ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            OracleParameter[] parameters = {
                    new OracleParameter(":tblName", OracleType.VarChar, 255),
                    new OracleParameter(":fldName", OracleType.VarChar, 255),
                    new OracleParameter(":PageSize", OracleType.Number),
                    new OracleParameter(":PageIndex", OracleType.Number),
                    new OracleParameter(":IsReCount", OracleType.Clob),
                    new OracleParameter(":OrderType", OracleType.Clob),
                    new OracleParameter(":strWhere", OracleType.VarChar,1000),
                    };
            parameters[0].Value = "SUPPLIER";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperOra.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  成员方法
    }
}
